using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Spread.Common.Features.Sample;
using FarPoint.Win;
using FarPoint.Win.Spread;
using FarPoint.Win.Spread.CellType;
using GrapeCity.Win.Spread.InputMan.CellType;
using GrapeCity.Win.Spread.InputMan.CellType.Fields;
using GrapeCity.Spreadsheet;

namespace Spread.DataValidation
{
    public partial class DataValidation : BaseSampleControl
    {
        private System.Drawing.Color _fclrHeader = System.Drawing.Color.White;
        private System.Drawing.Color _bclrHeader = System.Drawing.Color.Purple;
        private System.Drawing.Color _subHeaderColor = System.Drawing.Color.LightPink, _validationColor = System.Drawing.Color.LightGoldenrodYellow;

        private System.Drawing.Font _fntHeader = new System.Drawing.Font("Segoi Ui", 10, System.Drawing.FontStyle.Bold);
        private LineBorder _lineBorder = new LineBorder(System.Drawing.Color.Black);
        public DataValidation()
        {
            InitializeComponent();
            Name = "Excel-compatible Data Validation";

            var container = (this.pnlControls.Parent.Parent as SplitContainer);
            fpSpread.ActiveSheetChanged += (s, e) => container.Panel2Collapsed = !(fpSpread.ActiveSheetIndex == 0);
            
            #region Data Validation Introduction Sheet
            int row = 0, col = 0;
            _sheetIntro.SetColumnWidth(-1, 120);
            _sheetIntro.SetRowHeight(-1, 18);

            //Whole Number Validator
            SetHeaderAndBoundary(_sheetIntro, row, col, 2, 12, 1, 1,"Whole Number Validations");
            _sheetIntro.Cells[row + 3, col].BackColor = _sheetIntro.Cells[row + 5, col].BackColor = _sheetIntro.Cells[row + 7, col].BackColor = _subHeaderColor;
            _sheetIntro.Cells[row + 3, col + 1].BackColor = _sheetIntro.Cells[row + 5, col + 1].BackColor = _sheetIntro.Cells[row + 7, col + 1].BackColor = _validationColor;

            _sheetIntro.Cells[row + 3, col].Text = "Between :";
            _sheetIntro.Cells[row + 3, col + 1].Value = 5;
            _sheetIntro.Cells[row + 3, col + 1].Validation.Add(DataValidationType.WholeNumber, DataValidationErrorStyle.Stop, DataValidationOperator.Between, "1", "9");
            AddValidationActions(row + 3, col + 1, 1, 1, true, string.Empty, string.Empty, true, "Whole Number Validator", "This cell accepts only whole numbers between 1 and 9.");

            _sheetIntro.Cells[row + 5, col].Text = "Less than or equal :";
            _sheetIntro.Cells[row + 5, col + 1].Value = 50;
            _sheetIntro.Cells[row + 5, col + 1].Validation.Add(DataValidationType.WholeNumber, DataValidationErrorStyle.Stop, DataValidationOperator.LessThanOrEqual, "100");
            AddValidationActions(row + 5, col + 1, 1, 1, true, string.Empty, string.Empty, true, "Whole Number Validator", "This cell accepts only whole numbers <=100");

            _sheetIntro.Cells[row + 7, col].Text = "Formula :";
            _sheetIntro.Cells[row + 7, col + 1].Value = 30;
            _sheetIntro.Cells[row + 7, col + 1].Validation.Add(DataValidationType.WholeNumber, DataValidationErrorStyle.Stop, DataValidationOperator.GreaterThanOrEqual, "=B6/2");
            AddValidationActions(row + 7, col + 1, 1, 1, true, string.Empty, string.Empty, true, "Whole Number Validator", "This cell accepts only whole numbers >= (B6)/2");

            //Decimal and text length validators
            SetHeaderAndBoundary(_sheetIntro, row + 9, col, 2, 12, 1, 1, "TextLength & Decimal Validations");
            SubHeaderStyling(_sheetIntro, row + 12, col, 6, 4, 0, 2);
            _sheetIntro.SetClipValue(row + 12, col, 6, 4, "Product\tPrice\tTax\tTotal\n" +
                "Apple\t10\t0.05\nOrange\t20\t0.10\nCoconut\t35\t0.075\nPear\t30\t0.08\nGrape\t40\t0.09");
            _sheetIntro.Cells[row + 13, col + 1, row + 17, col + 1].CellType = new CurrencyCellType();
            _sheetIntro.Cells[row + 13, col + 2, row + 17, col + 2].CellType = new PercentCellType();
            _sheetIntro.Cells[row + 13, col + 3, row + 17, col + 3].CellType = new CurrencyCellType();
            _sheetIntro.Cells[row + 13, col + 3, row + 17, col + 3].Formula = "$B14+$B14*$C14";

            _sheetIntro.Cells[row + 13, col, row + 17, col].Validation.Add(DataValidationType.TextLength, DataValidationErrorStyle.Stop, DataValidationOperator.LessThan, "15");
            AddValidationActions(row + 13, col, 5, 1, true, string.Empty, string.Empty, true, string.Empty, "Text Length <15 characters");

            _sheetIntro.Cells[row + 13, col + 2, row + 17, col + 2].Validation.Add(DataValidationType.Decimal, DataValidationErrorStyle.Stop, DataValidationOperator.Between, "0.01", "0.1");
            AddValidationActions(row + 13, col + 2, 5, 1, true, string.Empty, string.Empty, true, string.Empty, "Tax must be between 1% and 10%");

            //List and Date validators
            SetHeaderAndBoundary(_sheetIntro, row + 19, col, 2, 12, 1, 1, "List & Date Validations");
            SubHeaderStyling(_sheetIntro, row + 22, col, 4, 5, 0, 1, 2);
            _sheetIntro.SetClipValue(row + 22, col, 1, 5, "Product\tFrom Date\tTo Date\tUnits Sold\tTotal Amount");
            _sheetIntro.Cells[row + 23, col + 1, row + 25, col + 2].CellType = new DateTimeCellType();
            _sheetIntro.Cells[row + 23, col + 3, row + 25, col + 3].CellType = new CurrencyCellType();

            _sheetIntro.Cells[row + 23, col, row + 25, col].Validation.Add(DataValidationType.List, DataValidationErrorStyle.Stop, DataValidationOperator.Equal, "=$A$14:$A$18");
            AddValidationActions(row + 23, col, 3, 1, true, string.Empty, string.Empty, true, string.Empty, string.Empty);

            _sheetIntro.Cells[row + 23, col + 1, row + 25, col + 1].Validation.Add(DataValidationType.Date, DataValidationErrorStyle.Stop, DataValidationOperator.Between, "=DATE(YEAR(NOW()),1,1)", "=TODAY()");
            AddValidationActions(row + 23, col + 1, 3, 1, true, string.Empty, string.Empty, true, string.Empty, "From Date must be between start & current date of current year");

            _sheetIntro.Cells[row + 23, col + 2, row + 25, col + 2].Validation.Add(DataValidationType.Date, DataValidationErrorStyle.Stop, DataValidationOperator.Between, "=$B24", "=TODAY()");
            AddValidationActions(row + 23, col + 2, 3, 1, true, string.Empty, string.Empty, true, string.Empty, "From Date must be between selected From Date & current date of current year");

            _sheetIntro.Cells[row + 23, col + 3, row + 25, col + 3].Formula = @"IF(OR($A24="""",$B24="""",$C24=""""), 0, DATEDIF($B24,$C24,""D"")) * 3";
            _sheetIntro.Cells[row + 23, col + 4, row + 25, col + 4].Formula = @"IF(OR($A24="""",$D24=0),0,VLOOKUP($A24,$A$14:$D$18,4,FALSE)) * $D24";

            //Time and Custom Validations
            SetHeaderAndBoundary(_sheetIntro, row + 27, col, 2, 12, 1, 1, "Time & Custom Validations");
            SubHeaderStyling(_sheetIntro, row+30, col, 6, 6, 0, 2, 3, 4);
            _sheetIntro.SetClip(row + 30, col, 6, 6, "ID\tName\tEmail\tEntry Time\tExit Time\tWork Duration\n" +
                "1\tEmily\tEmily@mescius.com\n2\tSarah\tSarah@mescius.com\n3\tLily\tLily@mescius.com\n4\tAnna\tAnna@mescius.com\n5\tDaisy\tDaisy@mescius.com");
            var timeSpanCellType = new GcTimeSpanCellType();
            timeSpanCellType.Fields.Clear();
            timeSpanCellType.Fields.AddRange(new TimeSpanFieldCollectionInfo()
            {
                new TimeSpanHourFieldInfo(2),
                new TimeSpanLiteralFieldInfo(":"),
                new TimeSpanMinuteFieldInfo(2),
                new TimeSpanLiteralFieldInfo(":"),
                new TimeSpanSecondFieldInfo(2),
            });
            _sheetIntro.Cells[row + 31, col + 3, row + 35, col + 4].CellType = timeSpanCellType;

            _sheetIntro.Cells[row + 31, col + 3, row + 35, col + 3].Validation.Add(DataValidationType.Time, DataValidationErrorStyle.Stop, DataValidationOperator.Between, "=TIME(8,0,0)", "=TIME(17,0,0)");
            AddValidationActions(row + 31, col + 3, 5, 1, true, string.Empty, string.Empty, true, string.Empty, "Entry time must be between 8Am and 5PM");
            _sheetIntro.Cells[row + 31, col + 4, row + 35, col + 4].Validation.Add(DataValidationType.Time, DataValidationErrorStyle.Stop, DataValidationOperator.Between, "=$D32", "=TIME(17,0,0)");
            AddValidationActions(row + 31, col + 4, 5, 1, true, string.Empty, string.Empty, true, string.Empty, "Exit time must be between Entry Time and 5PM");

            _sheetIntro.Cells[row + 31, col + 5, row + 35, col + 5].CellType = new DateTimeCellType() { DateTimeFormat = DateTimeFormat.TimeOnly };
            _sheetIntro.Cells[row + 31, col + 5, row + 35, col + 5].Formula = @"IF(OR($D32="""", $E32=""""),0, $E32-$D32)";

            _sheetIntro.Cells[row + 31, col + 2, row + 35, col + 2].Validation.Add(DataValidationType.Custom, DataValidationErrorStyle.Stop, DataValidationOperator.Between, @"=ISNUMBER(FIND(""@"",C32))");
            AddValidationActions(row + 31, col + 2, 5, 1, true, string.Empty, string.Empty, true, string.Empty, "Email must have a format of abc@xyz.");

            _sheetIntro.Cells[row + 31, col, row + 35, col].Validation.Add(DataValidationType.Custom, DataValidationErrorStyle.Stop, DataValidationOperator.Between, @"=COUNTIF($A$32:$A$36, $A32)=1");
            AddValidationActions(row + 31, col, 5, 1, true, string.Empty, string.Empty, true, string.Empty, "Values should be unique in the cell range $A$32:$A$36");
            #endregion

            #region Products sheet
            int startRow1 = 0, startCol1 = 0;
            _sheetProducts.Rows[startRow1].Height = 25;

            SetHeaderAndBoundary(_sheetProducts, startRow1, startCol1, 1, 1, 3, 1);
            _sheetProducts.SetClip(startRow1, startCol1, 3, 1, "Category\nMarker\nPen");

            SetHeaderAndBoundary(_sheetProducts, startRow1, startCol1+2, 1, 2, 5, 2);
            _sheetProducts.SetClip(startRow1, startCol1+2, 5, 1, "Markers\nGold\nPermanent\nSilver\nWhite paint");
            _sheetProducts.SetClip(startRow1+1, startCol1 +3, 4, 1, "4.29\n1.49\n3.99\n3.99");

            SetHeaderAndBoundary(_sheetProducts, startRow1, startCol1+5, 1, 2, 10, 2);
            _sheetProducts.SetClip(startRow1, startCol1+5, 10, 1, "Pens\nFlexgrip highlighter\nGripper ball\nRetractable\nroundedge stick pens\nRubber grip\nRubberstik\nStainless steel ballpoint\nStick pen\nTwist erase pencil");
            _sheetProducts.SetClip(startRow1 + 1, startCol1 + 6, 9, 1, "1.49\n1.29\n4.99\n1.2\n0.95\n0.69\n9.99\n0.59\n6.49");

            _sheetProducts.Cells[startRow1 + 1, startCol1 + 3, startRow1+4, startCol1+3].CellType = new CurrencyCellType();
            _sheetProducts.Cells[startRow1 + 1, startCol1 + 6, startRow1 + 9, startCol1 + 6].CellType = new CurrencyCellType();

            _sheetProducts.AddCustomName("Categories", "Products!$A$2:$A$3", 0, 0);
            _sheetProducts.AddCustomName("Marker", "Products!$C$2:$C$5", 0, 0);
            _sheetProducts.AddCustomName("MarkerPrice", "Products!$C$2:$D$5", 0, 0);
            _sheetProducts.AddCustomName("Pen", "Products!$F$2:$F$10", 0, 0);
            _sheetProducts.AddCustomName("PenPrice", "Products!$F$2:$G$10", 0, 0);
            #endregion

            #region OrdersForm Sheet
            int startRow2 = 2, startCol2 = 1;
            _sheetOrderForm.SetRowHeight(-1, 20);
            _sheetOrderForm.Rows[2].Height = 30;
            SetHeaderAndBoundary(_sheetOrderForm, startRow2, startCol2, 1, 4);
            _sheetOrderForm.Cells[startRow2, startCol2].Text = "Order Form";

            SetHeaderAndBoundary(_sheetOrderForm, startRow2, startCol2 + 4);
            _sheetOrderForm.Cells[startRow2, startCol2+4].Formula = "TODAY()";
            _sheetOrderForm.Cells[startRow2, startCol2 + 4].CellType = new DateTimeCellType();

            SetHeaderAndBoundary(_sheetOrderForm, startRow2 + 2, startCol2, 1, 1, 9, 5);
            _sheetOrderForm.SetClip(startRow2 + 2, startCol2, 1, 5, "Category\tDescription\tUnit Cost\tUnits\tTotal Cost");
            //Data Validations
            _sheetOrderForm.Validations.Add(startRow2 + 3, startCol2, startRow2 + 10, startCol2, GrapeCity.Spreadsheet.DataValidationType.List, GrapeCity.Spreadsheet.DataValidationErrorStyle.Warning, GrapeCity.Spreadsheet.DataValidationOperator.Equal, "=Categories");
            _sheetOrderForm.Validations.Add(startRow2 + 3, startCol2+1, startRow2 + 10, startCol2+1, GrapeCity.Spreadsheet.DataValidationType.List, GrapeCity.Spreadsheet.DataValidationErrorStyle.Warning, GrapeCity.Spreadsheet.DataValidationOperator.Equal, "=INDIRECT(B6)");
            //Formulas
            _sheetOrderForm.Cells[startRow2 + 3, startCol2 + 2, startRow2 + 10, startCol2 + 2].Formula = @"IF(C6="""","""",VLOOKUP(C6,INDIRECT(B6&""Price""),2,FALSE))";
            _sheetOrderForm.Cells[startRow2 + 3, startCol2 + 4, startRow2 + 10, startCol2 + 4].Formula = @"IF(C6="""","""",D6*E6)";
            //CellTypes
            _sheetOrderForm.Cells[startRow2 + 3, startCol2 + 2, startRow2 + 10, startCol2 + 2].CellType = new CurrencyCellType();
            _sheetOrderForm.Cells[startRow2 + 3, startCol2 + 4, startRow2 + 11, startCol2 + 4].CellType = new CurrencyCellType();
            _sheetOrderForm.Cells[startRow2 + 3, startCol2 + 3, startRow2 + 10, startCol2 + 3].Value = 10;

            SetHeaderAndBoundary(_sheetOrderForm, startRow2 + 11, startCol2, 1, 4);
            _sheetOrderForm.Cells[startRow2 + 11, startCol2].Text = "Sub Total:";
            SetHeaderAndBoundary(_sheetOrderForm, startRow2 + 11, startCol2+4, 1, 1);
            _sheetOrderForm.Cells[startRow2 + 11, startCol2+4].Formula = "SUM(F6:F13)";

            //GST and PST section
            _sheetOrderForm.Cells[startRow2 + 14, startCol2 + 2, startRow2 + 15, startCol2 + 4].Border = _lineBorder;
            _sheetOrderForm.Cells[startRow2 + 14, startCol2+2].CellType = new CheckBoxCellType { TextTrue = "PST", TextFalse = "PST" };
            _sheetOrderForm.Cells[startRow2 + 15, startCol2+2].CellType = new CheckBoxCellType { TextTrue = "GST", TextFalse = "GST" };

            _sheetOrderForm.Cells[startRow2 + 14, startCol2 + 3].CellType = new PercentCellType();
            _sheetOrderForm.Cells[startRow2 + 14, startCol2 + 3].Value = 0.08;
            _sheetOrderForm.Cells[startRow2 + 15, startCol2 + 3].CellType = new PercentCellType();
            _sheetOrderForm.Cells[startRow2 + 15, startCol2 + 3].Value = 0.07;

            _sheetOrderForm.Cells[startRow2 + 14, startCol2 + 4].Formula = "D17*F14*E17";
            _sheetOrderForm.Cells[startRow2 + 15, startCol2 + 4].Formula = "D18*F14*E18";
            
            SetHeaderAndBoundary(_sheetOrderForm, startRow2 + 16, startCol2 + 2, 1, 2);
            SetHeaderAndBoundary(_sheetOrderForm, startRow2 + 16, startCol2 + 4);
            _sheetOrderForm.Cells[startRow2 + 16, startCol2 + 2].Text = "Total:";
            _sheetOrderForm.Cells[startRow2 + 16, startCol2 + 4].Formula = "F14+F17+F18";
            _sheetOrderForm.Cells[startRow2 + 14, startCol2 + 4, startRow2 + 17, startCol2 + 4].CellType = new CurrencyCellType();
            #endregion
        }

        private void SetHeaderAndBoundary(SheetView sheet, int row, int col, int rowSpan=1, int colSpan=1, int rowCount=1, int colCount=1, string header="")
        {
            sheet.Cells[row, col].ColumnSpan = colSpan;
            sheet.Cells[row, col].RowSpan = rowSpan;
            sheet.Cells[row, col, row, col+colCount-1].BackColor = _bclrHeader;
            sheet.Cells[row, col, row, col+colCount-1].ForeColor = _fclrHeader;
            sheet.Cells[row, col, row, col+colCount-1].VerticalAlignment = CellVerticalAlignment.Center;
            sheet.Cells[row, col, row, col+colCount-1].Font = _fntHeader;
            sheet.Cells[row, col, row + rowCount - 1, col + colCount - 1].Border = _lineBorder;
            sheet.Cells[row, col].Value = header;
            sheet.Columns[col, col + colCount-1].Width = 120;
        }
        private void SubHeaderStyling(SheetView sheet, int row, int col, int rowCount, int colCount, params int[] colsWithValidation)
        {
            sheet.Cells[row, col, row, col + colCount - 1].BackColor = _subHeaderColor;
            sheet.Cells[row, col, row + rowCount - 1, col + colCount - 1].Border = new LineBorder(System.Drawing.Color.Black);
            foreach (var c in colsWithValidation)
            {
                sheet.Cells[row + 1, c, row + rowCount - 1, c].BackColor = _validationColor;
            }
        }

        private void _bAddValidation_Click(object sender, EventArgs e)
        {
            fpSpread.ShowDataValidationForm(this.ParentForm, true);
        }

        private void _bClearValidation_Click(object sender, EventArgs e)
        {
            var cellRange = _sheetIntro.GetSelection(0);
            _sheetIntro.Validations.Clear(cellRange.Row, cellRange.Column, cellRange.Row + cellRange.RowCount - 1, cellRange.Column + cellRange.ColumnCount - 1);
            _sheetIntro.Validations.ClearCircles();
            _sheetIntro.Validations.CircleInvalid();
        }

        private void _bClearAllValidations_Click(object sender, EventArgs e)
        {
            _sheetIntro.Validations.Clear();
            _sheetIntro.Validations.ClearCircles();
        }

        private void _bCircleInvalid_Click(object sender, EventArgs e)
        {
            _sheetIntro.Validations.CircleInvalid();
        }

        private void _bRemoveCircles_Click(object sender, EventArgs e)
        {
            _sheetIntro.Validations.ClearCircles();
        }

        private void AddValidationActions(int row, int col, int rowCount, int colCount, bool showErr = false, string errTitle = "", string errMsg = "", bool showInput = false, string inputTitle = "", string inputMsg = "")
        {
            int row2 = row + rowCount - 1;
            int col2 = col + colCount - 1;
            if (showErr)
            {
                _sheetIntro.Cells[row, col, row2, col2].Validation.ShowError = true;
                if (string.Empty != errTitle)
                    _sheetIntro.Cells[row, col, row2, col2].Validation.ErrorTitle = errTitle;
                if (string.Empty != errMsg)
                    _sheetIntro.Cells[row, col, row2, col2].Validation.ErrorMessage = errMsg;
            }
            if (showInput)
            {
                _sheetIntro.Cells[row, col, row2, col2].Validation.ShowInput = true;
                if (string.Empty != inputTitle)
                    _sheetIntro.Cells[row, col, row2, col2].Validation.InputTitle = inputTitle;
                if (string.Empty != inputMsg)
                    _sheetIntro.Cells[row, col, row2, col2].Validation.InputMessage = inputMsg;
            }
        }
    }
}


//
//DataValidation.Designer.cs
//


namespace Spread.DataValidation
{
    partial class DataValidation
    {
        /// <summary> 
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary> 
        /// Clean up any resources being used.
        /// </summary>
        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Component Designer generated code

        /// <summary> 
        /// Required method for Designer support - do not modify 
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            this.fpSpread = new FarPoint.Win.Spread.FpSpread();
            this._sheetIntro = new FarPoint.Win.Spread.SheetView();
            this._sheetOrderForm = new FarPoint.Win.Spread.SheetView();
            this._sheetProducts = new FarPoint.Win.Spread.SheetView();
            this._bAddValidation = new System.Windows.Forms.Button();
            this._bClearValidation = new System.Windows.Forms.Button();
            this._bClearAllValidations = new System.Windows.Forms.Button();
            this._bCircleInvalid = new System.Windows.Forms.Button();
            this._bRemoveCircles = new System.Windows.Forms.Button();
            this.pnlMain.SuspendLayout();
            this.pnlControls.SuspendLayout();
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(this._sheetIntro)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(this._sheetOrderForm)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(this._sheetProducts)).BeginInit();
            this.SuspendLayout();
            // 
            // pnlMain
            // 
            this.pnlMain.Controls.Add(this.fpSpread);
            this.pnlMain.Size = new System.Drawing.Size(568, 599);
            // 
            // pnlControls
            // 
            this.pnlControls.Controls.Add(this._bRemoveCircles);
            this.pnlControls.Controls.Add(this._bCircleInvalid);
            this.pnlControls.Controls.Add(this._bClearAllValidations);
            this.pnlControls.Controls.Add(this._bClearValidation);
            this.pnlControls.Controls.Add(this._bAddValidation);
            this.pnlControls.Size = new System.Drawing.Size(188, 599);
            // 
            // fpSpread
            // 
            this.fpSpread.Dock = System.Windows.Forms.DockStyle.Fill;
            this.fpSpread.Location = new System.Drawing.Point(0, 0);
            this.fpSpread.Name = "fpSpread";
            this.fpSpread.Sheets.AddRange(new FarPoint.Win.Spread.SheetView[] {
            this._sheetIntro,
            this._sheetOrderForm,
            this._sheetProducts});
            this.fpSpread.Size = new System.Drawing.Size(568, 599);
            this.fpSpread.TabIndex = 0;
            // 
            // _sheetIntro
            // 
            this._sheetIntro.Reset();
            this._sheetIntro.SheetName = "Introduction";
            // Formulas and custom names must be loaded with R1C1 reference style
            this._sheetIntro.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1;
            this._sheetIntro.RowHeader.Columns.Default.Resizable = false;
            this._sheetIntro.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1;
            // 
            // _sheetOrderForm
            // 
            this._sheetOrderForm.Reset();
            this._sheetOrderForm.SheetName = "Sample DataValidation";
            // Formulas and custom names must be loaded with R1C1 reference style
            this._sheetOrderForm.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1;
            this._sheetOrderForm.Cells.Get(1, 1).Locked = false;
            this._sheetOrderForm.Cells.Get(1, 2).Locked = false;
            this._sheetOrderForm.Cells.Get(1, 3).Locked = false;
            this._sheetOrderForm.Cells.Get(1, 4).Locked = false;
            this._sheetOrderForm.Cells.Get(1, 5).Locked = false;
            this._sheetOrderForm.Cells.Get(1, 6).Locked = false;
            this._sheetOrderForm.Cells.Get(1, 7).Locked = false;
            this._sheetOrderForm.Cells.Get(2, 1).Locked = false;
            this._sheetOrderForm.Cells.Get(2, 2).Locked = false;
            this._sheetOrderForm.Cells.Get(2, 3).Locked = false;
            this._sheetOrderForm.Cells.Get(2, 4).Locked = false;
            this._sheetOrderForm.Cells.Get(2, 5).Locked = false;
            this._sheetOrderForm.Cells.Get(2, 6).Locked = false;
            this._sheetOrderForm.Cells.Get(2, 7).Locked = false;
            this._sheetOrderForm.Cells.Get(3, 1).Locked = false;
            this._sheetOrderForm.Cells.Get(3, 2).Locked = false;
            this._sheetOrderForm.Cells.Get(3, 3).Locked = false;
            this._sheetOrderForm.Cells.Get(3, 4).Locked = false;
            this._sheetOrderForm.Cells.Get(3, 5).Locked = false;
            this._sheetOrderForm.Cells.Get(3, 6).Locked = false;
            this._sheetOrderForm.Cells.Get(3, 7).Locked = false;
            this._sheetOrderForm.Cells.Get(4, 1).Locked = false;
            this._sheetOrderForm.Cells.Get(4, 2).Locked = false;
            this._sheetOrderForm.Cells.Get(4, 3).Locked = false;
            this._sheetOrderForm.Cells.Get(4, 4).Locked = false;
            this._sheetOrderForm.Cells.Get(4, 5).Locked = false;
            this._sheetOrderForm.Cells.Get(4, 6).Locked = false;
            this._sheetOrderForm.Cells.Get(4, 7).Locked = false;
            this._sheetOrderForm.Cells.Get(5, 1).Locked = false;
            this._sheetOrderForm.Cells.Get(5, 2).Locked = false;
            this._sheetOrderForm.Cells.Get(5, 3).Locked = false;
            this._sheetOrderForm.Cells.Get(5, 4).Locked = false;
            this._sheetOrderForm.Cells.Get(5, 5).Locked = false;
            this._sheetOrderForm.Cells.Get(5, 6).Locked = false;
            this._sheetOrderForm.Cells.Get(5, 7).Locked = false;
            this._sheetOrderForm.Cells.Get(6, 1).Locked = false;
            this._sheetOrderForm.Cells.Get(6, 2).Locked = false;
            this._sheetOrderForm.Cells.Get(6, 3).Locked = false;
            this._sheetOrderForm.Cells.Get(6, 4).Locked = false;
            this._sheetOrderForm.Cells.Get(6, 5).Locked = false;
            this._sheetOrderForm.Cells.Get(6, 6).Locked = false;
            this._sheetOrderForm.Cells.Get(6, 7).Locked = false;
            this._sheetOrderForm.Cells.Get(7, 1).Locked = false;
            this._sheetOrderForm.Cells.Get(7, 2).Locked = false;
            this._sheetOrderForm.Cells.Get(7, 3).Locked = false;
            this._sheetOrderForm.Cells.Get(7, 4).Locked = false;
            this._sheetOrderForm.Cells.Get(7, 5).Locked = false;
            this._sheetOrderForm.Cells.Get(7, 6).Locked = false;
            this._sheetOrderForm.Cells.Get(7, 7).Locked = false;
            this._sheetOrderForm.Cells.Get(8, 1).Locked = false;
            this._sheetOrderForm.Cells.Get(8, 2).Locked = false;
            this._sheetOrderForm.Cells.Get(8, 3).Locked = false;
            this._sheetOrderForm.Cells.Get(8, 4).Locked = false;
            this._sheetOrderForm.Cells.Get(8, 5).Locked = false;
            this._sheetOrderForm.Cells.Get(8, 6).Locked = false;
            this._sheetOrderForm.Cells.Get(8, 7).Locked = false;
            this._sheetOrderForm.Cells.Get(9, 1).Locked = false;
            this._sheetOrderForm.Cells.Get(9, 2).Locked = false;
            this._sheetOrderForm.Cells.Get(9, 3).Locked = false;
            this._sheetOrderForm.Cells.Get(9, 4).Locked = false;
            this._sheetOrderForm.Cells.Get(9, 5).Locked = false;
            this._sheetOrderForm.Cells.Get(9, 6).Locked = false;
            this._sheetOrderForm.Cells.Get(9, 7).Locked = false;
            this._sheetOrderForm.Cells.Get(10, 1).Locked = false;
            this._sheetOrderForm.Cells.Get(10, 2).Locked = false;
            this._sheetOrderForm.Cells.Get(10, 3).Locked = false;
            this._sheetOrderForm.Cells.Get(10, 4).Locked = false;
            this._sheetOrderForm.Cells.Get(10, 5).Locked = false;
            this._sheetOrderForm.Cells.Get(10, 6).Locked = false;
            this._sheetOrderForm.Cells.Get(10, 7).Locked = false;
            this._sheetOrderForm.Cells.Get(11, 1).Locked = false;
            this._sheetOrderForm.Cells.Get(11, 2).Locked = false;
            this._sheetOrderForm.Cells.Get(11, 3).Locked = false;
            this._sheetOrderForm.Cells.Get(11, 4).Locked = false;
            this._sheetOrderForm.Cells.Get(11, 5).Locked = false;
            this._sheetOrderForm.Cells.Get(11, 6).Locked = false;
            this._sheetOrderForm.Cells.Get(11, 7).Locked = false;
            this._sheetOrderForm.Cells.Get(12, 1).Locked = false;
            this._sheetOrderForm.Cells.Get(12, 2).Locked = false;
            this._sheetOrderForm.Cells.Get(12, 3).Locked = false;
            this._sheetOrderForm.Cells.Get(12, 4).Locked = false;
            this._sheetOrderForm.Cells.Get(12, 5).Locked = false;
            this._sheetOrderForm.Cells.Get(12, 6).Locked = false;
            this._sheetOrderForm.Cells.Get(12, 7).Locked = false;
            this._sheetOrderForm.Cells.Get(13, 1).Locked = false;
            this._sheetOrderForm.Cells.Get(13, 2).Locked = false;
            this._sheetOrderForm.Cells.Get(13, 3).Locked = false;
            this._sheetOrderForm.Cells.Get(13, 4).Locked = false;
            this._sheetOrderForm.Cells.Get(13, 5).Locked = false;
            this._sheetOrderForm.Cells.Get(13, 6).Locked = false;
            this._sheetOrderForm.Cells.Get(13, 7).Locked = false;
            this._sheetOrderForm.Cells.Get(14, 1).Locked = false;
            this._sheetOrderForm.Cells.Get(14, 2).Locked = false;
            this._sheetOrderForm.Cells.Get(14, 3).Locked = false;
            this._sheetOrderForm.Cells.Get(14, 4).Locked = false;
            this._sheetOrderForm.Cells.Get(14, 5).Locked = false;
            this._sheetOrderForm.Cells.Get(14, 6).Locked = false;
            this._sheetOrderForm.Cells.Get(14, 7).Locked = false;
            this._sheetOrderForm.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1;
            // 
            // _sheetProducts
            // 
            this._sheetProducts.Reset();
            this._sheetProducts.SheetName = "Products";
            // Formulas and custom names must be loaded with R1C1 reference style
            this._sheetProducts.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1;
            this._sheetProducts.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1;
            // 
            // _bAddValidation
            // 
            this._bAddValidation.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left) 
            | System.Windows.Forms.AnchorStyles.Right)));
            this._bAddValidation.Location = new System.Drawing.Point(15, 19);
            this._bAddValidation.Name = "_bAddValidation";
            this._bAddValidation.Size = new System.Drawing.Size(121, 23);
            this._bAddValidation.TabIndex = 0;
            this._bAddValidation.Text = "Add Validation To Cells";
            this._bAddValidation.UseVisualStyleBackColor = true;
            this._bAddValidation.Click += new System.EventHandler(this._bAddValidation_Click);
            // 
            // _bClearValidation
            // 
            this._bClearValidation.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left) 
            | System.Windows.Forms.AnchorStyles.Right)));
            this._bClearValidation.Location = new System.Drawing.Point(15, 48);
            this._bClearValidation.Name = "_bClearValidation";
            this._bClearValidation.Size = new System.Drawing.Size(121, 23);
            this._bClearValidation.TabIndex = 1;
            this._bClearValidation.Text = "Clear Validation From Cells";
            this._bClearValidation.UseVisualStyleBackColor = true;
            this._bClearValidation.Click += new System.EventHandler(this._bClearValidation_Click);
            // 
            // _bClearAllValidations
            // 
            this._bClearAllValidations.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left) 
            | System.Windows.Forms.AnchorStyles.Right)));
            this._bClearAllValidations.Location = new System.Drawing.Point(15, 77);
            this._bClearAllValidations.Name = "_bClearAllValidations";
            this._bClearAllValidations.Size = new System.Drawing.Size(121, 23);
            this._bClearAllValidations.TabIndex = 2;
            this._bClearAllValidations.Text = "Clear All Validations";
            this._bClearAllValidations.UseVisualStyleBackColor = true;
            this._bClearAllValidations.Click += new System.EventHandler(this._bClearAllValidations_Click);
            // 
            // _bCircleInvalid
            // 
            this._bCircleInvalid.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left) 
            | System.Windows.Forms.AnchorStyles.Right)));
            this._bCircleInvalid.Location = new System.Drawing.Point(15, 139);
            this._bCircleInvalid.Name = "_bCircleInvalid";
            this._bCircleInvalid.Size = new System.Drawing.Size(121, 23);
            this._bCircleInvalid.TabIndex = 3;
            this._bCircleInvalid.Text = "Circle Invalid Cells";
            this._bCircleInvalid.UseVisualStyleBackColor = true;
            this._bCircleInvalid.Click += new System.EventHandler(this._bCircleInvalid_Click);
            // 
            // _bRemoveCircles
            // 
            this._bRemoveCircles.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left) 
            | System.Windows.Forms.AnchorStyles.Right)));
            this._bRemoveCircles.Location = new System.Drawing.Point(16, 168);
            this._bRemoveCircles.Name = "_bRemoveCircles";
            this._bRemoveCircles.Size = new System.Drawing.Size(120, 23);
            this._bRemoveCircles.TabIndex = 4;
            this._bRemoveCircles.Text = "Remove Circles";
            this._bRemoveCircles.UseVisualStyleBackColor = true;
            this._bRemoveCircles.Click += new System.EventHandler(this._bRemoveCircles_Click);
            // 
            // DataValidation
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.Name = "DataValidation";
            this.Size = new System.Drawing.Size(757, 599);
            this.pnlMain.ResumeLayout(false);
            this.pnlControls.ResumeLayout(false);
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread)).EndInit();
            ((System.ComponentModel.ISupportInitialize)(this._sheetIntro)).EndInit();
            ((System.ComponentModel.ISupportInitialize)(this._sheetOrderForm)).EndInit();
            ((System.ComponentModel.ISupportInitialize)(this._sheetProducts)).EndInit();
            this.ResumeLayout(false);

        }

        #endregion

        private FarPoint.Win.Spread.FpSpread fpSpread;
        private FarPoint.Win.Spread.SheetView _sheetProducts;
        private FarPoint.Win.Spread.SheetView _sheetOrderForm;
        private FarPoint.Win.Spread.SheetView _sheetIntro;
        private System.Windows.Forms.Button _bRemoveCircles;
        private System.Windows.Forms.Button _bCircleInvalid;
        private System.Windows.Forms.Button _bClearAllValidations;
        private System.Windows.Forms.Button _bClearValidation;
        private System.Windows.Forms.Button _bAddValidation;
    }
}
